package com.ylkj.wx.dao.impl;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.ylkj.common.base.BaseDaoHibernate;
import com.ylkj.wx.dao.LawyerInfoQueryDAO;
import com.ylkj.wx.dto.LawyerInfoDTO;
import com.ylkj.wx.dto.QuestionListDTO;

@Repository
public class LawyerInfoQueryDAOImpl extends BaseDaoHibernate implements LawyerInfoQueryDAO {

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<LawyerInfoDTO> findLawyerInfoByParam(final Map<String, Object> param) {
		List<LawyerInfoDTO> list = (List<LawyerInfoDTO>) getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List ret = null;
				StringBuffer hql = new StringBuffer();
				hql.append("SELECT a.`phr_code` AS phrCode, a.`union_id` AS unionId, a.`photo_url` AS photoUrl,a.`nick_name` AS nickName,");
				hql.append(" a.`lawyer_flag` AS lawyerFlag, b.`sex_code` AS sexCode, b.`sex_name` AS sexName,b.`lawyer_name` AS lawyerName,");
				hql.append(" b.`phone_num` AS phoneNum, b.`province_code` AS provinceCode, b.`province` AS province,b.`city_code` AS cityCode,");
				hql.append(" b.`city` AS city, b.`area_code` AS areaCode, b.`area` AS area, b.`lawfirm_name` AS lawfirmName, b.special_skill AS specialSkill,");
				hql.append(" b.`attentioned_count` AS attentionedCount, b.`help_count` AS helpCount, b.`service_area` AS serviceArea,");
				hql.append(" b.`case_num` AS caseNum, b.`work_begin_date` AS workBeginDate,b.`office_tel` AS officeTel,b.`audit_flag` AS auditFlag,");
				hql.append(" b.`level` AS level FROM `user_info` a, `lawyer_info` b WHERE a.`phr_code`=b.`phr_code` ");
				if (param.get("provinceCode") != null && !param.get("provinceCode").equals("")) {
					hql.append(" and b.province_code=:provinceCode");
				}
				if (param.get("cityCode") != null && !param.get("cityCode").equals("")) {
					hql.append(" and b.city_code=:cityCode");
				}
				if (param.get("areaCode") != null && !param.get("areaCode").equals("")) {
					hql.append(" and b.area_code=:areaCode");
				}
				if (param.get("specialSkill") != null && !param.get("specialSkill").equals("")) {
					hql.append(" and b.special_skill in (:specialSkill)");
				}

				Query query = session.createSQLQuery(hql.toString())
						.addScalar("phrCode").addScalar("unionId")
						.addScalar("photoUrl").addScalar("nickName").addScalar("lawyerFlag",Hibernate.STRING)
						.addScalar("sexCode").addScalar("sexName").addScalar("lawyerName")
						.addScalar("phoneNum").addScalar("provinceCode").addScalar("province")
						.addScalar("cityCode").addScalar("city").addScalar("areaCode")
						.addScalar("area").addScalar("lawfirmName").addScalar("specialSkill")
						.addScalar("attentionedCount").addScalar("helpCount").addScalar("serviceArea")
						.addScalar("caseNum").addScalar("workBeginDate");
				
				if (param.get("provinceCode") != null && !param.get("provinceCode").equals("")) {
					query.setParameter("provinceCode", param.get("provinceCode"));
				}
				if (param.get("cityCode") != null && !param.get("cityCode").equals("")) {
					query.setParameter("cityCode", param.get("cityCode"));
				}
				if (param.get("areaCode") != null && !param.get("areaCode").equals("")) {
					query.setParameter("areaCode", param.get("areaCode"));
				}
                if (param.get("specialSkill") != null) {
					List list = (List) param.get("specialSkill");
                    if (list.size() > 0) {
                        query.setParameterList("specialSkill", list);
                    }
                }
                
                ret = query.setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(LawyerInfoDTO.class))
                .list();
				return ret;
			}
		});
		
		return list;
	}

}
